package cn.tedu.dao;

import cn.tedu.entity.Student;
import cn.tedu.utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {
    public void insert(Student student){
        //获取连接
        try( Connection conn= DBUtils.getConn()) {
            String sql="insert into student values(null,?,?,?,?)";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1,student.getName());
            ps.setString(2,student.getDepartment());
            ps.setInt(3,student.getGrade());
            ps.setInt(4,student.getStudent());
            ps.executeUpdate();
            System.out.println("添加完成!");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public List<Student> findAll() {
        ArrayList<Student> list=new ArrayList<>();
        //获取连接
        try( Connection conn=DBUtils.getConn()) {
            String sql="select*from student";
            Statement s=conn.createStatement();
            ResultSet rs=s.executeQuery(sql);
            while(rs.next()){
                int id=rs.getInt(1);
                String name=rs.getString(2);
                String department=rs.getString(3);
                int grade=rs.getInt(4);
                int student=rs.getInt(5);
                list.add(new Student(id,name,department,grade,student));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public void deleteById(String id) {
        //获取连接
        try( Connection conn=DBUtils.getConn()) {
            String sql="delete from student where id=?";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(id));
            ps.executeUpdate();
            System.out.println("删除成功!");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public Student findById(String id) {
        //获取连接
        try( Connection conn=DBUtils.getConn()) {
            String sql="select*from student where id=?";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(id));
            ResultSet rs=ps.executeQuery();
            if(rs.next()){
                String name=rs.getString(2);
                String department=rs.getString(3);
                int grade=rs.getInt(4);
                int student=rs.getInt(5);
                return new Student(Integer.parseInt(id),name,department,grade,student);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public void update(Student student1) {
        //获取连接
        try( Connection conn=DBUtils.getConn()) {
            String sql="update student set name=?,department=?,grade=?,student=? where id=?";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1,student1.getName());
            ps.setString(2,student1.getDepartment());
            ps.setInt(3,student1.getGrade());
            ps.setInt(4,student1.getStudent());
            ps.setInt(5,student1.getId());
            ps.executeUpdate();
            System.out.println("修改完成!");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public Student findByName(String name) {
       //获取连接
       try( Connection conn=DBUtils.getConn()) {
           String sql="select * from student where name=?";
           PreparedStatement ps=conn.prepareStatement(sql);
           ps.setString(1,name);
           ResultSet rs=ps.executeQuery();
           while(rs.next()){
               int id=rs.getInt("id");
               String department=rs.getString("department");
               int grade=rs.getInt("grade");
               int student=rs.getInt("student");
               return new Student(id,name,department,grade,student);

           }
       } catch (Exception e) {
           e.printStackTrace();
       }
       return null;
    }
}
